package com.xm.dao.background;

import com.xm.entity.*;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import com.xm.util.LogisticsUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 田顺琪
 * @date 2024-01-02
 */
public class WarehouseDao extends BaseUtil {

    /**
     * 查看仓库商品详情
     * @param warehouseId 仓库ID
     * @return List<对象>
     */
    public Page<CommodityWarehouse> getCondition(int pageNumber,int pageSize,int warehouseId,String commodityMc) {
        List<CommodityWarehouse> list=new ArrayList<>();
        Page<CommodityWarehouse> page=new Page<>();
        @Language("sql")
        String countSql = "SELECT COUNT(*) FROM commodity_warehouse cw " +
                "JOIN commodity c ON c.commodity_id = cw.commodity_id where warehouse_id = ?";
        @Language("sql")
        String sql = "SELECT cw.details_inventory, cw.purchase_limit, c.commodity_name,w.warehouse_id,cw.commodity_id" +
                ",w.warehouse_admin,w.warehouse_capacity FROM commodity_warehouse cw" +
                "         JOIN commodity c ON c.commodity_id = cw.commodity_id" +
                "         JOIN warehouse w ON cw.warehouse_id = w.warehouse_id" +
                " WHERE w.warehouse_id = ?";
        if(commodityMc!=null){
            if (isNumeric(commodityMc)){
                countSql+=" and cw.commodity_id like '%"+commodityMc+"%'";
                sql+=" and cw.commodity_id like '%"+commodityMc+"%'";
            }else{
                countSql+=" and c.commodity_name like '%"+commodityMc+"%'";
                sql+=" and c.commodity_name like '%"+commodityMc+"%'";
            }
        }
        sql+=" LIMIT ?, ?";
        ResultSet rsCondition = null;
        ResultSet countRs = null;
        try {
            countRs = executeQuery(countSql,warehouseId);
            if (countRs.next()) {
                int count = countRs.getInt(1);
                page.setCount(count);
                page.setTotalPages(pageSize);
            }
            // 分页查询数据
            int offset = (pageNumber - 1) * pageSize;
            rsCondition = executeQuery(sql,warehouseId,offset,pageSize);
            while (rsCondition.next()) {
                CommodityWarehouse commodityWarehouse = new CommodityWarehouse(
                        rsCondition.getInt("cw.details_inventory"),
                        rsCondition.getInt("cw.purchase_limit"),
                        rsCondition.getInt("w.warehouse_id"),
                        rsCondition.getInt("w.warehouse_admin"),
                        new Commodity(rsCondition.getString("c.commodity_name"),rsCondition.getInt("cw.commodity_id"))
                );
                list.add(commodityWarehouse);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rsCondition);
            close(countRs);
        }
        page.setData(list);
        page.setPageNumber(pageNumber);
        page.setPageSize(pageSize);
        return page;
    }

    /**
     * 分页条件模糊查询
     * @param pageNumber 分页页数
     * @param pageSize 每页数量
     * @param warehouseMc 搜索内容
     * @return page<对象>
     */
    public Page<Warehouse> pagination(int pageNumber, int pageSize,String warehouseMc) {
        Page<Warehouse> page = new Page<>();
        List<Warehouse> list = new ArrayList<>();
        @Language("sql")
        String countSql = "SELECT COUNT(*) FROM warehouse";
        @Language("sql")
        String dataSql = "SELECT * FROM warehouse w" +
                " JOIN administrator_information ai ON w.warehouse_admin=ai.information_id where 1=1";
        if(warehouseMc!=null){
            if (isNumeric(warehouseMc)){
                dataSql+=" and warehouse_id like '%"+warehouseMc+"%'";
            }else{
                dataSql+=" and warehouse_name like '%"+warehouseMc+"%'";
            }
        }
            dataSql+=" LIMIT ?, ?";
        ResultSet countRs = null;
        ResultSet dataRs = null;
        try {
            // 查询总数
            countRs = executeQuery(countSql);
            if (countRs.next()) {
                int count = countRs.getInt(1);
                page.setCount(count);
                page.setTotalPages(pageSize);
            }
            // 分页查询数据
            int offset = (pageNumber - 1) * pageSize;
            dataRs = executeQuery(dataSql, offset, pageSize);
            while (dataRs.next()) {
                Warehouse warehouse = extractWarehouseFromResultSet(dataRs);
                list.add(warehouse);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(countRs);
            close(dataRs);
        }
        page.setData(list);
        page.setPageNumber(pageNumber);
        page.setPageSize(pageSize);
        return page;
    }
    /**
     * 查询全部仓库找出离收货地点最近的货源Id
     * @return Integer 最近货仓Id
     */
    public Integer getAllWarehouses(double myLatitude,double myLongitude) {
        @Language("sql")
        String sql = "SELECT * FROM warehouse";
        ResultSet rs = null;
        Warehouse warehouse = new Warehouse();
        try {
            rs = executeQuery(sql);
            while (rs.next()) {
                warehouse.setWarehouseId(rs.getInt("warehouse_id"));
                warehouse.setWarehouseLongitude(rs.getString("warehouse_longitude"));
                warehouse.setWarehouseLatitude(rs.getString("warehouse_latitude"));

                LogisticsUtil.addLogisticsCompany(warehouse.getWarehouseId(),
                        Double.parseDouble(warehouse.getWarehouseLatitude()),
                        Double.parseDouble(warehouse.getWarehouseLongitude()));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }
        // 返回离指定位置最近的物流公司Id
        return LogisticsUtil.getNearestCompany(myLatitude, myLongitude);
    }
    /**
     * 根据仓库ID查询仓库信息
     * @param warehouseId 仓库ID
     * @return Warehouse对象
     */
    public Warehouse getWarehouseById(int warehouseId) {
        @Language("sql")
        String sql = "SELECT * FROM warehouse w" +
                " JOIN administrator_information ai ON w.warehouse_admin=ai.information_id" +
                " WHERE w.warehouse_id = ?";
        ResultSet rs = null;
        Warehouse warehouse = null;
        try {
            rs = executeQuery(sql, warehouseId);
            if (rs.next()) {
                warehouse = extractWarehouseFromResultSet(rs);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }
        return warehouse;
    }
    /**
     * 查询除指定仓库ID以外的所有仓库信息
     * @param excludeId 需要排除的仓库ID
     * @return List<Warehouse>对象
     */
    public List<Warehouse> getAllWarehousesExcept(int excludeId) {
        @Language("sql")
        String sql = "SELECT * FROM warehouse w" +
                " JOIN administrator_information ai ON w.warehouse_admin=ai.information_id" +
                " WHERE w.warehouse_id != ?";
        ResultSet rs = null;
        List<Warehouse> warehouseList = new ArrayList<>();
        try {
            rs = executeQuery(sql, excludeId);
            while (rs.next()) {
                Warehouse warehouse = extractWarehouseFromResultSet(rs);
                warehouseList.add(warehouse);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }
        return warehouseList;
    }
    /**
     * 从 ResultSet 中获取 Warehouse 对象
     */
    private Warehouse extractWarehouseFromResultSet(ResultSet rs) throws SQLException {
        int warehouseId = rs.getInt("warehouse_id");
        int warehouseCapacity = rs.getInt("warehouse_capacity");
        String warehouseName = rs.getString("warehouse_name");
        String warehouseProvince = rs.getString("warehouse_province");
        String warehouseCity = rs.getString("warehouse_city");
        String warehouseDistrict = rs.getString("warehouse_district");
        String warehouseFullAddress = rs.getString("warehouse_full_address");
        String warehouseLongitude = rs.getString("warehouse_longitude");
        String warehouseLatitude = rs.getString("warehouse_latitude");
        AdministratorInformation administratorInformation=new AdministratorInformation();
        administratorInformation.setName(rs.getString("name"));
        return new Warehouse(warehouseId, warehouseName, warehouseProvince, warehouseCity, warehouseDistrict, warehouseFullAddress, warehouseLongitude, warehouseLatitude,administratorInformation,warehouseCapacity);
    }

    /**
     *
     * @param str 参数
     * @return true:数字/false:字符串
     */
    public static boolean isNumeric(String str) {
        return str != null && str.matches("-?\\d+(\\.\\d+)?");
    }

    /**
     * 修改库存和限购
     * @param inventory 库存
     * @param limit 限购
     * @param commodityId 商品编号
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean update(int inventory,int limit,int commodityId) throws SqlErrorException {
        @Language("sql")
        String sql="update commodity_warehouse set details_inventory=?,purchase_limit=? where commodity_id=?";
        Object[] params = {inventory, limit,commodityId};
        int result = executeUpdate(sql, params);
        return result==1;
    }
}
